/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package quanlisinhvien.core;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 *
 * @author Marshmallow
 */
public class MySQLConnect {
       
    Properties props = new Properties();
    Connection connect = null; //Khởi tạo connection = null
    Statement statement = null ; //Khởi tạo Statement để thực thi truy vấn
    ResultSet result = null; //Khởi tạo ResultSet để chứa dữ liệu trả về

    public MySQLConnect() {
        try{
            this.props.load(new FileInputStream("config.properties"));            
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
        }
    }
    
    //Hàm kiểm tra driver đã sẵn sàng hay chưa
    protected void driverTest() throws Exception{
        try{
            Class.forName(this.props.getProperty("DriverClassName"));            
        }
        catch (java.lang.ClassNotFoundException e){
            throw new Exception("MySQL JDBC Driver not found.");
        }
    }
    
    //Hàm lấy connection
    protected Connection getConnect() throws Exception{        
        if (this.connect == null){
            driverTest();            
            String host = this.props.getProperty("Host");
            String port = this.props.getProperty("Port");
            String database = this.props.getProperty("Database");
            String userName = this.props.getProperty("UserName");
            String password = this.props.getProperty("Password");            
            //Tạo chuỗi kết nối 
            String url = "jdbc:mysql://" + host + ":"+ port +"/" + database + "?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true";            
            try{
                
                this.connect = DriverManager.getConnection(url, userName, password);
            }
            catch (java.sql.SQLException e){
                //throw new Exception("Can not connect to Database");
                System.out.println(e.getMessage());
            }
        }
        return this.connect;
    }

    //Tạo Statement để thực thi câu Query
    protected Statement getStatement() throws Exception{
        if(this.statement == null ? true :this.statement.isClosed()){
            this.statement = this.getConnect().createStatement();
        }
        return this.statement;
    }
    
    //Hàm thực thi câu Select lấy dữ liệu từ database
    public ResultSet executeQuery(String Query) throws Exception{
        try{
            //System.out.println(Query);
            this.result = this.getStatement().executeQuery(Query);
        }
        catch (SQLException e){
            throw new Exception("Error" + e.getMessage() + "-" + Query);
        }
        return this.result;
    }
    
    //Hàm thực thi các câu lệnh Insert Update Delete
    public int executeUpdate(String Query) throws Exception{
        int res = Integer.MIN_VALUE;
        try{
            //System.out.println(Query);
            res = this.getStatement().executeUpdate(Query); //trả về số lượng dòng mà ở dưới csdl bị tác động hoặc sẽ trả về 0
        }
        catch (SQLException e){
            throw new Exception("Error : " + e.getMessage()+ "-" + Query );
        }
        finally{
            this.Close();
        }
        return res;
    }
    
    //Hàm đóng kết nối
    private void Close() throws Exception{
        if ( (this.result != null) && (!this.result.isClosed()) ){
            this.result.close();
            this.result = null;
        }
        if ( (this.statement != null) && (!this.statement.isClosed()) ){
            this.statement.close();
            this.statement = null;
        }
        if ( (this.connect != null) && (!this.connect.isClosed()) ){
            this.connect.close();
            this.connect = null;
        }    
    }   
    
}
